# 封装数据库工具类
import pymysql


class DBUtil():
    conn = None

    @classmethod
    def __get_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host='211.103.136.244', port=7061, user='student',
                                       passwd='iHRM_student_2021', database='ihrm', charset='utf8')
        return cls.conn

    @classmethod
    def __close_conn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 查询一条
    @classmethod
    def select_one(cls, sql):
        cur = None
        rows = None
        try:
            cls.conn = cls.__get_conn()
            cur = cls.conn.cursor()
            cur.execute(sql)
            rows = cur.fetchone()
        except Exception as e:
            print("查询sql失败：", e)
        finally:
            cls.__close_conn()
            return rows

    # 增删改
    @classmethod
    def uid_up(cls, sql):
        cur = None
        try:
            # 2.建立连接
            cls.conn = cls.__get_conn()
            # 3.获取游标
            cur = cls.conn.cursor()
            # 4.执行insert语句
            cur.execute(sql)
            print("影响行数：", cls.conn.affected_rows())
            # 5.提交 事务
            cls.conn.commit()
        except Exception as e:
            print("增删改 sql执行失败：", e)
            # 回滚事务
            cls.conn.rollback()
        finally:
            # 6.关闭游标
            cls.__close_conn()


# if __name__ == '__main__':
#     res = DBUtil.select_one("select * from bs_user")
#     print(res)

    # def select_many(self,size):
    #     conn = pymysql.connect(host='localhost', port=3306, user='root',
    #                            passwd='123456', db='test_db', charset='utf8')
    #     cur = conn.cursor()
    #     cur.execute("select * from t_book")
    #     rows = cur.fetchmany(size)
    #     print(rows)
    #     cur.close()
    #     conn.close()
    #     return rows
    #
    # def select_all(self):
    #     conn = pymysql.connect(host='localhost', port=3306, user='root',
    #                            passwd='123456', db='test_db', charset='utf8')
    #     cur = conn.cursor()
    #     cur.execute("select * from t_book")
    #     rows = cur.fetchall()
    #     print(rows)
    #     cur.close()
    #     conn.close()
    #     return rows
    #
    # #添加
    # def insert_one(self):
    #     conn= None
    #     cur = None
    #     try:
    #         get_conn()
    #         cur = conn.cursor()
    #         cur.execute("insert into t_book(id,title,pub_data) values (22,'大话西游','1996-02-09')")
    #         conn.commit()
    #     except Exception as e:
    #         print(e)
    #         conn.rollback()
    #     finally:
    #         cur.close()
    #         conn.close()
    #
    # #修改
    # def update_one(self):
    #     conn = None
    #     cur = None
    #     try:
    #         conn = pymysql.connect(host='localhost', port=3306, user='root',
    #                                passwd='123456', db='test_db', charset='utf8')
    #         cur = conn.cursor()
    #         cur.execute("update t_book set `read` = `read`+1 where id = 27")
    #         conn.commit()
    #     except Exception as e:
    #         print(e)
    #         conn.rollback()
    #     finally:
    #         cur.close()
    #         conn.close()
    #
    # #删除
    # def delete_one(self):
    #     conn = None
    #     cur = None
    #     try:
    #         conn = pymysql.connect(host='localhost', port=3306, user='root',
    #                                passwd='123456', db='test_db', charset='utf8')
    #         cur = conn.cursor()
    #         cur.execute("delete from t_book where id = 22")
    #         conn.commit()
    #     except Exception as e:
    #         print(e)
    #         conn.rollback()
    #     finally:
    #         cur.close()
    #         conn.close()
